package com.zretc.device.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zretc.dbtools.DBTools;
import com.zretc.device.entity.Device_Return;

public class Device_ReturnDao {
	/**
	 * 增加设备还库
	 * 
	 * @param dreturn
	 * @return
	 */
	public boolean addDevReturn(Device_Return dreturn) {
		String sql = "insert into device_return values" + "(?,?,?,?,?,?,?)";
		int n = DBTools.getUpdate(sql, dreturn.getReturn_id(), dreturn.getDevice_code(), dreturn.getReturn_date(),
				dreturn.getNormal_num(), dreturn.getDamage_num(), dreturn.getKeeper(), dreturn.getReturn_person());
		if (n==1) {
			String updateSql ="UPDATE device set device.now_num =device.now_num+(select device_return.normal_num from device_return where device_return.return_id=?) where device.device_code  = ?";
			DBTools.getUpdate(updateSql, dreturn.getReturn_id(),dreturn.getDevice_code());
			String updateDamage ="UPDATE device set device.total_num =device.total_num-(select device_return.damage_num from device_return where device_return.return_id=?) where device.device_code  = ?";
			DBTools.getUpdate(updateDamage, dreturn.getReturn_id(),dreturn.getDevice_code());
		}
		
		return n > 0;
	}

	/**
	 * 查询所有
	 * 
	 * @return
	 */
	public List<Device_Return> findDevReturnAll() {
		// 创建集合
		List<Device_Return> list = new ArrayList<Device_Return>();
		String sql = "select * from device_return";
		ResultSet rs = DBTools.getQuery(sql, null);
		try {
			while (rs.next()) {
				// 还库编号
				int return_id = rs.getInt(1);
				// 设备编号
				String device_code = rs.getString(2);
				// 归还日期
				String return_date = rs.getString(3);
				// 可以正常使用的数量
				int normal_num = rs.getInt(4);
				// 损坏的数量
				int damage_num = rs.getInt(5);
				// 仓库管理者
				String keeper = rs.getString(6);
				// 归还人
				String return_person = rs.getString(7);
				Device_Return dreturn = new Device_Return(return_id, device_code, return_date, normal_num, damage_num,
						keeper, return_person);
				// 加到集合中
				list.add(dreturn);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 删除的方法
	 * 
	 * @param return_id 还库id
	 * @return
	 */
	public boolean delDevReturn(int return_id) {
		Device_Return dr=findDevReturnById(return_id);
		String sql = "delete from device_return where return_id =?";
		int n = DBTools.getUpdate(sql, return_id);
		if (n==1) {
			//更新库存表
			String updateSql ="UPDATE device set device.now_num =device.now_num-? where device.device_code  = ?";
			DBTools.getUpdate(updateSql, dr.getNormal_num(),dr.getDevice_code());
			String updateDamage ="UPDATE device set device.total_num =device.total_num+? where device.device_code  = ?";
			DBTools.getUpdate(updateDamage, dr.getDamage_num(),dr.getDevice_code());
		}
		return n > 0;
	}

	/**
	 * 修改的方法
	 * @return
	 */
	public boolean modDevReturn(Device_Return devReturn) {
		//找回原来的值
		Device_Return dr=findDevReturnById(devReturn.getReturn_id());
		String sql="update device_return set device_code=?,return_date=?,normal_num=?,damage_num=?,"
				+ "keeper=?,return_person=? where return_id=?";
		int n=DBTools.getUpdate(sql, devReturn.getDevice_code(),devReturn.getReturn_date(),devReturn.getNormal_num(),
				devReturn.getDamage_num(),devReturn.getKeeper(),devReturn.getReturn_person(),devReturn.getReturn_id());
		if (n==1) {
			//更新库存表
			String updateSql ="UPDATE device set device.now_num =device.now_num-?+(select device_return.normal_num from device_return where device_return.return_id=?) where device.device_code  = ?";
			DBTools.getUpdate(updateSql, dr.getNormal_num(),devReturn.getReturn_id(),devReturn.getDevice_code());
			String updateDamage ="UPDATE device set device.total_num =device.total_num+?-(select device_return.damage_num from device_return where device_return.return_id=?) where device.device_code  = ?";
			DBTools.getUpdate(updateDamage, dr.getDamage_num(),devReturn.getReturn_id(),devReturn.getDevice_code());
		}
		return n>0;
	}
	/**
	 * 通过日期查找
	 * @return
	 */
	public List<Device_Return> findDevReturnByDate(String returnDate) {
		// 创建集合
		List<Device_Return> list = new ArrayList<Device_Return>();
		String sql = "select * from device_return where return_date like ?";
		ResultSet rs = DBTools.getQuery(sql, "%"+returnDate+"%");
		try {
			while (rs.next()) {
				// 还库编号
				int return_id = rs.getInt(1);
				// 设备编号
				String device_code = rs.getString(2);
				// 归还日期
				String return_date = rs.getString(3);
				// 可以正常使用的数量
				int normal_num = rs.getInt(4);
				// 损坏的数量
				int damage_num = rs.getInt(5);
				// 仓库管理者
				String keeper = rs.getString(6);
				// 归还人
				String return_person = rs.getString(7);
				Device_Return dreturn = new Device_Return(return_id, device_code, return_date, normal_num, damage_num,
						keeper, return_person);
				// 加到集合中
				list.add(dreturn);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//释放资源
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	/**
	 * 通过单号查找
	 * @return
	 */
	public Device_Return findDevReturnById(int returnId) {
		// 创建对象
		Device_Return dreturn =null;
		String sql = "select * from device_return where return_id=?";
		ResultSet rs = DBTools.getQuery(sql, returnId);
		try {
			if (rs.next()) {
				// 还库编号
				int return_id = rs.getInt(1);
				// 设备编号
				String device_code = rs.getString(2);
				// 归还日期
				String return_date = rs.getString(3);
				// 可以正常使用的数量
				int normal_num = rs.getInt(4);
				// 损坏的数量
				int damage_num = rs.getInt(5);
				// 仓库管理者
				String keeper = rs.getString(6);
				// 归还人
				String return_person = rs.getString(7);
				dreturn = new Device_Return(return_id, device_code, return_date, normal_num, damage_num,
						keeper, return_person);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//释放资源
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return dreturn;
	}
	
	/**
	 * 通过设备编号模糊查找
	 * @return
	 */
	public List<Device_Return> findDevReturnByCode(String devCode) {
		// 创建集合
		List<Device_Return> list = new ArrayList<Device_Return>();
		String sql = "select * from device_return where device_code like ?";
		ResultSet rs = DBTools.getQuery(sql, "%"+devCode+"%");
		try {
			if (rs.next()) {
				// 还库编号
				int return_id = rs.getInt(1);
				// 设备编号
				String device_code = rs.getString(2);
				// 归还日期
				String return_date = rs.getString(3);
				// 可以正常使用的数量
				int normal_num = rs.getInt(4);
				// 损坏的数量
				int damage_num = rs.getInt(5);
				// 仓库管理者
				String keeper = rs.getString(6);
				// 归还人
				String return_person = rs.getString(7);
				Device_Return dreturn = new Device_Return(return_id, device_code, return_date, normal_num, damage_num,
						keeper, return_person);
				list.add(dreturn);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//释放资源
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	

}
